﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for Payment
/// </summary>
public class Payment
{
	public Payment()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    //Insert transaction record into Transaction table
    public int insertTransaction(int accountID,decimal amount,bool isDeposite,DateTime transactionDate,  string productType)
    {
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "insert into [TRANSACTION] (accountID, amount, transactionDate, transactionType, productType) "
           + " values (@accountID,@amount,@transactionDate,@transactionType,@productType);SELECT SCOPE_IDENTITY()";
        SqlCommand cmd = new SqlCommand(sql, conn);

        var transactionType = "Withdrawal";
        if (isDeposite)
        {
            transactionType = "Deposite";
            amount = 0 - amount;
        }

        cmd.Parameters.Add(new SqlParameter("@accountID", (object)accountID ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@amount", amount));
        cmd.Parameters.Add(new SqlParameter("@transactionDate", (object)DateTime.Now ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@transactionType", transactionType));
        cmd.Parameters.Add(new SqlParameter("@productType", "RETIREMENT"));

        //cmd.ExecuteNonQuery();
        object newId = cmd.ExecuteScalar();
        int newIdInt = int.Parse(((Decimal)newId).ToString());
        conn.Close();

        return newIdInt; 
    }

   //Insert transaction record into R_PAYMENT
   public void pay(int accountID, string policyNo, decimal investAmount,decimal totalChargesAmount, decimal waiverAmount, decimal amount,string actionType,bool isDeposite,int transactionId)
   {
       SqlConnection conn = DBManager.getSqlConnection();
       conn.Open();

        string sql = "insert R_PAYMENT " +
                         " (policy_no, payment_datetime, total_charges_amount, invest_amount, waiver_rider_amount, manage_amount, type, amount,transaction_id)" +
                   " Values(@policy_no, @payment_datetime, @total_charges_amount, @invest_amount, @waiver_rider_amount, @manage_amount, @type, @amount,@transaction_id)";

        SqlCommand cmd = new SqlCommand(sql, conn);

       cmd.Parameters.Add(new SqlParameter("@policy_no", (object)policyNo ?? DBNull.Value));
       cmd.Parameters.Add(new SqlParameter("@payment_datetime", (object)DateTime.Now ?? DBNull.Value));
       cmd.Parameters.Add(new SqlParameter("@total_charges_amount", (object)totalChargesAmount ?? DBNull.Value));
       cmd.Parameters.Add(new SqlParameter("@invest_amount", (object)investAmount ?? DBNull.Value));
       cmd.Parameters.Add(new SqlParameter("@waiver_rider_amount", (object)waiverAmount ?? DBNull.Value));
       cmd.Parameters.Add(new SqlParameter("@manage_amount", DBNull.Value));
       cmd.Parameters.Add(new SqlParameter("@type", actionType));
       cmd.Parameters.Add(new SqlParameter("@amount", (object)amount ?? DBNull.Value));
       cmd.Parameters.Add(new SqlParameter("@transaction_id", transactionId));

       cmd.ExecuteNonQuery();
       conn.Close();

   }

   public void payPremium(int accountID, string policyNo, decimal investAmount, decimal totalChargesAmount, decimal waiverAmount, decimal amount, int transactionId)
   {
       pay(accountID,policyNo,investAmount,totalChargesAmount,waiverAmount,amount,"PREMIUM",false,transactionId);

    }

   public void payWithdrawal(int accountID, string policyNo, decimal amount)
   {
       payToClient(accountID, policyNo, amount, "WITHDRAWAL");

    }

   public void payTerminate(int accountID, string policyNo, decimal amount)
   {
       payToClient(accountID, policyNo, amount, "TERMINATE");
   }

   public void payClaim(int accountID, string policyNo, decimal amount)
   {
       payToClient(accountID, policyNo, amount, "CLAIM");
   }

   public void payToClient(int accountID, string policyNo, decimal amount, string type)
   {
       SqlConnection conn = DBManager.getSqlConnection();
       conn.Open();

       string sql = "insert into TRANSACTION (accountID, amount, transactionDate, transactionType, productType) "
           + " values (@accountID,@amount,@transactionDate,@transactionType,@productType)";
       SqlCommand cmd = new SqlCommand(sql, conn);

       var transactionType = "Withdrawal";
       cmd.Parameters.Add(new SqlParameter("@accountID", (object)accountID ?? DBNull.Value));
       cmd.Parameters.Add(new SqlParameter("@amount", amount));
       cmd.Parameters.Add(new SqlParameter("@transactionDate", (object)DateTime.Now ?? DBNull.Value));
       cmd.Parameters.Add(new SqlParameter("@transactionType", transactionType));
       cmd.Parameters.Add(new SqlParameter("@productType", "RETIREMENT"));

       sql = "insert into R_PAYMENT " +
                      " (policy_no, payment_datetime, type, amount)" +
                " Values(@policy_no, @payment_datetime, @type, @amount)";

       cmd = new SqlCommand(sql, conn);
       cmd.Parameters.Add(new SqlParameter("@policy_no", (object)policyNo ?? DBNull.Value));
       cmd.Parameters.Add(new SqlParameter("@payment_datetime", (object)DateTime.Now ?? DBNull.Value));
       cmd.Parameters.Add(new SqlParameter("@type", (object)type ?? DBNull.Value));
       cmd.Parameters.Add(new SqlParameter("@amount", (object)amount ?? DBNull.Value));

       cmd.ExecuteNonQuery();
       conn.Close();

       AccountsManager accountManager = new AccountsManager();

       int custId = accountManager.getCustIdFromAccount(accountID);

       string alertMsg = "";

       if(type=="CLAIM"){
           alertMsg = "Claim amount has been credited to your account";
       }
       else if (type == "WITHDRAWAL")
       {
           alertMsg = "Withdrawal amount has been credited to your account";
       }
       else if (type == "TERMINATE")
       {
           alertMsg = "Account value has been credited to your account";

       }else if( type == "PREMIUM"){
           alertMsg = "Premium has been debited from your account";
       }

       Alert.sendAlert(custId, type, alertMsg, "");
   }

   public static Decimal getTotalPremiumPaid(String policyNo)
   {
      DataTable dt = DBManager.GetData("select sum(A.AMOUNT+A.WAIVER_RIDER_AMOUNT) from R_PAYMENT A WHERE A.TYPE='PREMIUM' AND A.POLICY_NO='" + policyNo + "'");

      
      if (dt != null && dt.Rows.Count > 0)
      {

          if (dt.Rows[0][0] == DBNull.Value)
          {
              return 0;
          }
          else
          {
              return (Decimal)dt.Rows[0][0];
          }
      }

       return 0;
   }
}